﻿using System;
using System.Data.Common;
using System.Data.SqlClient;
using System.Diagnostics;
using Oracle.DataAccess.Client;
using Types.Enumerations.CCG;

namespace DataService.CrudClassGenerator
{
    /// <summary>
    /// Class for logging
    /// </summary>
    public class CCGLog : DataObjectHelper
    {
        #region [CONSTRUCTORS] ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

        /// <summary>
        /// Used to create a CCGLog instance
        /// </summary>
        /// <returns></returns>
        public static CCGLog Instance()
        {
            return new CCGLog();
        }
        /// <summary>
        /// Used to create a CCGLog instance
        /// </summary>
        /// <param name="pFullLogTableName">Full name of log table. e.g: CCG_TEST.LOG.CRUD_LOG or CCG_TEST.LOG.CRUD_LOG_ADHOC</param>
        /// <param name="pForAdHocQuery">Is the given table name for AdHoc query or not </param>
        /// <returns></returns>
        public static CCGLog Instance(string pFullLogTableName, bool pForAdHocQuery = false)
        {
            return new CCGLog(pFullLogTableName, pForAdHocQuery);
        }

        /// <summary>
        /// Used to create a CCGLog instance
        /// </summary>
        /// <returns></returns>
        public CCGLog()
        {
            FullLogTableName = "FullLogTableName".ReadAppSetting<string>();
            FullAdHocQueryLogTableName = "FullAdHocQueryLogTableName".ReadAppSetting<string>();
        }
        /// <summary>
        /// Used to create a CCGLog instance
        /// </summary>
        /// <param name="pFullLogTableName">Full name of log table. e.g: CCG_TEST.LOG.CRUD_LOG or CCG_TEST.LOG.CRUD_LOG_ADHOC</param>
        /// <param name="pForAdHocQuery">Is the given table name for AdHoc query or not </param>
        /// <returns></returns>
        public CCGLog(string pFullLogTableName, bool pForAdHocQuery = false)
        {
            if (!string.IsNullOrEmpty(pFullLogTableName))
            {
                if (!pForAdHocQuery)
                    FullLogTableName = pFullLogTableName;
                else
                    FullAdHocQueryLogTableName = pFullLogTableName;
            }
        }

        #endregion

        #region [PARAMETERS ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

        private string mIdentityPrmName = string.Empty;
        private string mFullLogTableName = string.Empty;
        private string mFullAdHocQueryLogTableName = string.Empty;

        /// <summary>
        /// Full name of log table. e.g: CCG_TEST.LOG.CRUD_LOG
        /// </summary>
        public string FullLogTableName
        {
            get { return mFullLogTableName; }
            set { mFullLogTableName = value; }
        }

        /// <summary>
        /// Full name of adhoc query log table. e.g: CCG_TEST.LOG.CRUD_LOG_ADHOC
        /// </summary>
        public string FullAdHocQueryLogTableName
        {
            get { return mFullAdHocQueryLogTableName; }
            set { mFullAdHocQueryLogTableName = value; }
        }

        #endregion

        #region [METHODS] ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

        /// <summary>
        /// Used to generate insert query to insert logs to logging table
        /// </summary>
        /// <returns></returns>
        public string GetLogTableInsertQuery()
        {
            if (DataServiceUtil.DatabaseType() == DatabaseTypes.MS_SQL)
            {
                return GetLogTableInsertQuery("@pPKValue");
            }
            else if (DataServiceUtil.DatabaseType() == DatabaseTypes.ORACLE)
            {
                return GetLogTableInsertQuery(":pPKVALUE");
            }
            else
            {
                throw new ApplicationException(DataServiceUtil.UnknownDbType);
            }
        }

        /// <summary>
        /// Used to generate insert query to insert logs to logging table
        /// </summary>
        /// <param name="pIdentityPrmName">PK (Primary Key) parameter name which is used for PKValue field</param>
        /// <returns></returns>
        public string GetLogTableInsertQuery(string pIdentityPrmName)
        {
            mIdentityPrmName = pIdentityPrmName;

            string sql = string.Empty;

            if (DataServiceUtil.DatabaseType() == DatabaseTypes.MS_SQL)
            {
                sql = @"INSERT INTO {0}
                                   (UserID,TableName,CrudType,CrudDate,PKValue,CrudValues) 
                            VALUES (@pUserID,@pTableName,@pCrudType,GETDATE(),{1},@pCrudValues)";
            }
            else if (DataServiceUtil.DatabaseType() == DatabaseTypes.ORACLE)
            {
                sql = @"INSERT INTO {0}
                                   (CRUDUSERID,CRUDTABLENAME,CRUDTYPE,CRUDDATE,PKVALUE,CRUDVALUES) 
                            VALUES (:pCRUDUSERID,:pCRUDTABLENAME,:pCRUDTYPE,SYSDATE,{1},:pCRUDVALUES)";
            }
            else
            {
                throw new ApplicationException(DataServiceUtil.UnknownDbType);
            }

            sql = string.Format(sql, mFullLogTableName, mIdentityPrmName);
            return sql;
        }

        /// <summary>
        /// Used to generate command object for logging.
        /// This metod is called from generated classes
        /// </summary>
        /// <param name="pCmd">Command object to be added logging parameters</param>
        /// <param name="pUserID">Unique identifying information of user who made crud process. 
        /// This identification may be national ID number or e-mail adres. Or anything you want.</param>
        /// <param name="pTableName">The table that was performed on the crud</param>
        /// <param name="pPrimaryKey">PK (Primary Key) value of row was performed on the crud</param>
        /// <param name="pCrudType">Crud type. "I" for Insert"U" for Update"D" for Delete</param>
        /// <param name="pCrudValues">Old values of row was performed on the crud</param>
        public void AddLogCommand(DbCommand pCmd, string pUserID, string pTableName, string pPrimaryKey, CrudTypes pCrudType, string pCrudValues)
        {
            int pkValue;
            int.TryParse(pPrimaryKey, out pkValue);

            string crudType = "N";
            switch (pCrudType)
            {
                case CrudTypes.Insert: crudType = "I"; break;
                case CrudTypes.Update: crudType = "U"; break;
                case CrudTypes.Delete: crudType = "D"; break;
            }

            if (string.IsNullOrEmpty(pUserID)) pUserID = ProcessorUserID;

            if (DataServiceUtil.DatabaseType() == DatabaseTypes.MS_SQL)
            {
                #region SQL Parameters

                ((SqlCommand)pCmd).Parameters.AddWithValue("@pUserID", pUserID);
                ((SqlCommand)pCmd).Parameters.AddWithValue("@pTableName", pTableName);
                ((SqlCommand)pCmd).Parameters.AddWithValue("@pCrudType", crudType);

                if (!string.IsNullOrEmpty(pPrimaryKey) && (pkValue > 0))
                    ((SqlCommand)pCmd).Parameters.AddWithValue(mIdentityPrmName, pPrimaryKey);

                if (!string.IsNullOrEmpty(pCrudValues))
                    ((SqlCommand)pCmd).Parameters.AddWithValue("@pCrudValues", pCrudValues);

                #endregion
            }
            else if (DataServiceUtil.DatabaseType() == DatabaseTypes.ORACLE)
            {
                #region ORACLE Parameters

                ((OracleCommand)pCmd).Parameters.Add(":pCRUDUSERID", pUserID);
                ((OracleCommand)pCmd).Parameters.Add(":pCRUDTABLENAME", pTableName);

                ((OracleCommand)pCmd).Parameters.Add(":pCRUDTYPE", crudType);

                if (!string.IsNullOrEmpty(pPrimaryKey) && (pkValue > 0))
                    ((OracleCommand)pCmd).Parameters.Add(mIdentityPrmName, pPrimaryKey);

                if (!string.IsNullOrEmpty(pCrudValues))
                    ((OracleCommand)pCmd).Parameters.Add(":pCRUDVALUES", pCrudValues);

                #endregion
            }
            else
            {
                throw new ApplicationException(DataServiceUtil.UnknownDbType);
            }
        }

        /// <summary>
        /// Used to generate command object from adhoc queries for logging.
        /// </summary>
        /// <param name="pParameters">Parameter collection to be acted on</param>
        /// <returns></returns>
        public DbCommand GetAdHocQueryLogCommand(DbParameterCollection pParameters)
        {
            StackTrace stackTrace = new StackTrace();
            string methodName = stackTrace.GetFrame(3).GetMethod().ToString();

            int frameIndx = 0;
            if (stackTrace.GetFrame(3).GetMethod().ReflectedType != null) frameIndx = 3;
            else if (stackTrace.GetFrame(2).GetMethod().ReflectedType != null) frameIndx = 2;
            else if (stackTrace.GetFrame(1).GetMethod().ReflectedType != null) frameIndx = 1;

            string nameSapceAndClassName = stackTrace.GetFrame(frameIndx).GetMethod().ReflectedType != null ? stackTrace.GetFrame(frameIndx).GetMethod().ReflectedType.FullName : string.Empty;


            methodName = string.Format("{0}#{1}", nameSapceAndClassName, methodName);

            string prmValue = string.Empty;
            foreach (DbParameter prm in pParameters)
            {
                if (prm.Value != null)
                    prmValue += string.Format(";{0} = {1}", prm.ParameterName, prm.Value.ToString());
            }
            prmValue = prmValue.TrimStart(';');

            string sql = string.Empty;

            if (DataServiceUtil.DatabaseType() == DatabaseTypes.MS_SQL)
            {
                sql = string.Format(@"
						INSERT INTO {0}
							   (UserID,MethodName,ParametersValues,CrudDate)
						 VALUES
							   (@pUserID,@pMethodName,@pParametersValues,GETDATE())
						", mFullAdHocQueryLogTableName);

                SqlCommand cmd = new SqlCommand(sql);
                cmd.Parameters.AddWithValue("@pUserID", ProcessorUserID);
                cmd.Parameters.AddWithValue("@pMethodName", methodName);
                cmd.Parameters.AddWithValue("@pParametersValues", prmValue);

                return cmd;
            }
            else if (DataServiceUtil.DatabaseType() == DatabaseTypes.ORACLE)
            {
                sql = string.Format(@"
						INSERT INTO {0}
							   (USERID,METHODNAME,PARAMETERSVALUES,CRUDDATE)
						 VALUES
							   (:pUSERID,:pMETHODNAME,:pPARAMETERSVALUES,SYSDATE)
						", mFullAdHocQueryLogTableName);

                OracleCommand cmd = new OracleCommand(sql);
                cmd.Parameters.Add(":pUSERID", ProcessorUserID);
                cmd.Parameters.Add(":pMETHODNAME", methodName);
                cmd.Parameters.Add(":pPARAMETERSVALUES", prmValue);

                return cmd;
            }
            else
            {
                throw new ApplicationException(DataServiceUtil.UnknownDbType);
            }
        }

        #endregion
    }
}
